﻿-------- Danh sách các dơn đặt hàng --------------------
CREATE 
--ALTER 
PROC proc_DanhSachDonHang

AS
BEGIN
	SELECT * FROM DONHANG
END

EXEC proc_DanhSachDonHang


---------------------- Liệt kê Danh sách Đơn hàng Theo Mã Khách Hàng ------------------------
CREATE
--ALTER
PROC proc_DonHangTheoMaKH
@makh varchar(6)
AS
BEGIN TRAN 
	--SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRY
		IF(@makh not in (select MaKH from DONHANG))
		BEGIN
			PRINT @makh + N'khong ton tai'
			ROLLBACK TRAN
			RETURN
		END
		Select * from DONHANG Where MaKH = @makh
		WAITFOR DELAY '0:00:10'
		Select * from DONHANG Where MaKH = @makh
	END TRY
	BEGIN CATCH
		RAISERROR (N'Loi He Thong',16,1)
		ROLLBACK TRAN
		RETURN
	END CATCH
COMMIT TRAN
GO

EXEC proc_DonHangTheoMaKH 'KH001'


------------ Thêm một một Đơn Hàng vào csdl --------------------------
CREATE 
--ALTER 
PROC proc_ThemDonHang
@madh varchar(6),@makh varchar(6), @ngaydh datetime, @matt int, @maNVDH varchar(6),@tongtien float,@res int output
AS
BEGIN TRAN
	--waitfor delay '0:0:10'
	BEGIN TRY
		IF(@madh not in (Select MaDH From DONHANG))
		BEGIN
			Insert into DONHANG(MaDH,MaKH,NgayGioDH,MaTT,MaNVDH,TongTien) values (@madh,@makh,@ngaydh,@matt,@maNVDH,@tongtien)
			set @res = 1
		END
		ELSE 
		BEGIN
			--PRINT 'lap hoa don that bai'
			set @res = 0	
		END
	END TRY
	BEGIN CATCH
		--RAISERROR (N'LỖI HỆ THỐNG',16,1)
		--RAISERROR 
		
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		ROLLBACK TRAN
		RETURN
	END CATCH
COMMIT TRAN
GO

Declare @res int
EXEC proc_ThemDonHang 'DH013','KH001','09/09/2012','1','NV003',30000,@res output
print @res


SELECT * FROM DONHANG 

----------huy_DON_HANG----------------------------------------
create 
--alter 
proc usp_HuyDSDH_KH
@madh varchar(6), @res int output
as
begin tran
	begin try
	if(@madh in(select MaDH from DONHANG))
	begin
		Delete from DONHANG where MaDH = @madh
		set @res = 1;
	end
	else
	begin
		set @res = 0;
	end
	end try
	begin catch 
		
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Loi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return	
	end catch
commit tran